/*
 * $RCSfile: SqlPlusAction.java,v $
 * $Revision: 1.1 $
 * $Date: 2013-03-19 $
 *
 * Copyright (C) 2008 Skin, Inc. All rights reserved.
 *
 * This software is the proprietary information of Skin, Inc.
 * Use is subject to license terms.
 */
package com.skin.webcat.action;

import java.io.IOException;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.skin.j2ee.action.BaseAction;
import com.skin.j2ee.annotation.UrlPattern;
import com.skin.j2ee.util.DaoUtil;
import com.skin.j2ee.util.JsonUtil;
import com.skin.webcat.database.Column;
import com.skin.webcat.database.Database;
import com.skin.webcat.database.Table;
import com.skin.webcat.database.dialect.Dialect;
import com.skin.webcat.database.sql.SqlPlus;
import com.skin.webcat.util.Jdbc;
import com.skin.webcat.util.Webcat;
import com.skin.webcat.web.WebcatServlet;

/**
 * <p>Title: SqlPlusAction</p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2006</p>
 * @author xuesong.net
 * @version 1.0
 */
public class SqlPlusAction extends BaseAction {
    private static final Logger logger = LoggerFactory.getLogger(SqlPlusAction.class);

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/sqlplus.html")
    public void query() throws ServletException, IOException {
        String name = this.getTrimString("name");
        String database = this.getTrimString("database");
        String tableName = this.getTrimString("tableName");
        this.setAttribute("name", name);
        this.setAttribute("database", database);
        this.setAttribute("tableName", tableName);
        this.forward("/template/webcat/sqlplus.jsp");
    }

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/sqlplus/execute.html")
    public void execute() throws ServletException, IOException {
        String name = this.getTrimString("name");
        String database = this.getTrimString("database");
        String sql = this.getTrimString("sql");
        WebcatServlet.execute(this.request, this.response, name, database, sql);
    }

    /**
     * @throws ServletException
     * @throws IOException
     */
    @UrlPattern("/webcat/sqlplus/setValue.html")
    public void setValue() throws ServletException, IOException {
        String name = this.getTrimString("name");
        String database = this.getTrimString("database");
        String tableName = this.getTrimString("tableName");
        String columnName = this.getTrimString("columnName");
        Connection connection = null;

        try {
            connection = Webcat.getConnection(name, database);
            Dialect dialect = Database.getDialect(connection);
            Table table = dialect.getTable(database, null, tableName);
            Column column = table.getColumn(columnName);
            List<Column> primaryKeys = table.getPrimaryKeys();

            if(column == null) {
                JsonUtil.error(this.request, this.response, 501, columnName + " not exists.");
                return;
            }

            if(primaryKeys == null || primaryKeys.size() < 1) {
                JsonUtil.error(this.request, this.response, 502, "primaryKeys is empty.");
                return;
            }

            Object value = SqlPlus.cast(this.getParameter("columnValue"), column.getDataType());
            List<Object> parameterList = this.getParameterList(this.request, primaryKeys);
            parameterList.add(0, value);

            String sql = this.getUpdateSql(tableName, columnName, primaryKeys);
            logger.info("sql: {}", sql);
            logger.info("parameters: {}", this.toString(parameterList));

            Object[] parameters = parameterList.toArray(new Object[parameterList.size()]);
            DaoUtil.execute(connection, sql, parameters);
            JsonUtil.success(this.request, this.response, true);
        }
        catch(Exception e) {
            JsonUtil.error(this.request, this.response, 999, e.getMessage());
            logger.error(e.getMessage(), e);
        }
        finally {
            Jdbc.close(connection);
        }
    }

    /**
     * @param tableName
     * @param columnName
     * @param primaryKeyList
     * @return String
     */
    private String getUpdateSql(String tableName, String columnName, List<Column> primaryKeyList) {
        if(primaryKeyList == null || primaryKeyList.size() < 1) {
            return null;
        }

        StringBuilder buffer = new StringBuilder();
        buffer.append("update ");
        buffer.append(tableName);
        buffer.append(" set ");
        buffer.append(columnName);
        buffer.append("=? where ");

        for(Column primaryKey : primaryKeyList) {
            buffer.append(primaryKey.getColumnName());
            buffer.append("=?");
            buffer.append(" and ");
        }
        buffer.delete(buffer.length() - 5, buffer.length());
        return buffer.toString();
    }

    /**
     * @param request
     * @param columns
     * @return List<Object>
     * @throws Exception
     */
    private List<Object> getParameterList(HttpServletRequest request, List<Column> columns) throws Exception {
        List<Object> result = new ArrayList<Object>();

        for(Column column : columns) {
            result.add(this.getValue(request, column));
        }
        return result;
    }

    /**
     * @param request
     * @param column
     * @return Object
     * @throws Exception
     */
    private Object getValue(HttpServletRequest request, Column column) throws Exception {
        int dataType = column.getDataType();
        String value = request.getParameter("_" + column.getColumnName());
        return SqlPlus.cast(value, dataType);
    }

    /**
     * @param list
     * @return String
     */
    private String toString(List<Object> list) {
        if(list == null) {
            return "null";
        }

        StringBuilder buffer = new StringBuilder();
        buffer.append("[");

        for(Object object : list) {
            if(object != null) {
                buffer.append(object.getClass().getSimpleName());
                buffer.append(": ");
                buffer.append(object.toString());
            }
            else {
                buffer.append("null");
            }
            buffer.append(", ");
        }

        if(buffer.length() >= 3) {
            buffer.delete(buffer.length() - 2, buffer.length());
        }
        buffer.append("]");
        return buffer.toString();
    }
}
